﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace HM.DataAccessLayer
{
    /// <summary>
    /// Class containing all the SQl queries to use for getting stats from HM
    /// </summary>
    public static class SqlStatements
    {

        /// <summary>
        /// Format a Date into a Postgres SQL Date
        /// </summary>
        /// <param name="date">The date to format</param>
        /// <param name="useEnglishFormat">Use English Format (true to format as english format, false to use french format)</param>
        /// <returns></returns>
        private static string _formatSQLDateToString(DateTime date, bool useEnglishFormat)
        {
            return useEnglishFormat ? date.ToString("yyyy/MM/dd HH:mm:ss") : date.ToString("dd/MM/yyyy HH:mm:ss");

        }

        /// <summary>
        /// Query to get basic stats for a player and a periode
        /// </summary>
        /// <param name="playerId">The id of the player</param>
        /// <param name="startDate">The start Date</param>
        /// <param name="endDate">The end date</param>
        /// <returns></returns>
        public static string GetSqlHandsRakeAmountWon(Int32 playerId,DateTime startDate,DateTime endDate )
        {

            return
                string.Format(
                    @" SELECT count(pokerhand_id) as nbhands, sum(rakeamount) as rakeamount, sum(netamountwon) as netamountwon 
                            FROM playerhandscashkeycolumns where (player_id = {0})
	                        AND handtimestamp Between TIMESTAMP '{1}' AND TIMESTAMP '{2}';",
                    playerId, _formatSQLDateToString(startDate, true), _formatSQLDateToString(endDate, true));

        }


        /// <summary>
        /// Query to get the player Id of a Poker Site
        /// </summary>
        /// <param name="playerName">The player Name </param>
        /// <param name="pokerSites">The PokerSite to find the player</param>
        public static string GetPokerSitePlayerId(string playerName,PokerSites pokerSites)
        {
            
            return
                string.Format(@"SELECT * from players where Lower(playername) = '{0}' AND site_id in (SELECT site_id
		                    FROM pokersites where Lower(Abbreviation)='{1}')",playerName.ToLower(),pokerSites.ToString().ToLower());

        }


        /// <summary>
        /// Query to get full stats for a player and a periode, and grouped by gametype
        /// </summary>
        /// <param name="playerId">The id of the player</param>
        /// <param name="startDate">The start Date</param>
        /// <param name="endDate">The end date</param>
        /// <returns></returns>
        public static string GetSqlDetailledStats(Int32 playerId, DateTime startDate, DateTime endDate)
        {

            return
                string.Format(@"select gt.gametypedescription as gametype
                ,gt.bigblind as bigblindvalue
                 ,count(ph.*) as totalhands,sum(ph.netamountwon)/100.0 as netamountwon
                ,sum(ph.netamountwon/1.0/gt.bigblind) as totalbbs,sum(case when didvpip = true then 1 else 0 end) as vpiphands
                , sum(case when ph.preflopaction_id <> -1 then 1 else 0 end) as totalplayablehands
                ,sum(case when didpfr = true then 1 else 0 end) as pfrhands
                ,sum(case when ph.preflopaction_id = 3 or ph.preflopaction_id = 4 then 1 else 0 end) as couldthreebet
                ,sum(case when ph.firstpreflopactiontype_id = 3 and (ph.preflopaction_id = 3 or ph.preflopaction_id = 4) then 1 else 0 end) as didthreebet
                ,sum(case when ph.maxstreetseen = 4 then 1 else 0 end) as sawshowdown,sum(case when ph.maxstreetseen >= 1 then 1 else 0 end) as sawflop
                ,sum(case when ph.netamountwon > 0 and ph.maxstreetseen = 4 then 1 else 0 end) as wonshowdown,sum(ph.totalflopbets) as flopbets
                ,sum(ph.totalflopcalls) as flopcalls,sum(ph.totalturnbets) as turnbets,sum(ph.totalturncalls) as turncalls,sum(ph.totalriverbets) as riverbets
                ,sum(ph.totalrivercalls) as rivercalls,sum(case when ph.totalflopbets > 0 then 1 else 0 end) as betonflop
                ,sum(case when ph.totalturnbets > 0 then 1 else 0 end) as betonturn,sum(case when ph.maxstreetseen >= 2 then 1 else 0 end) as sawturn
                ,sum(case when ph.totalriverbets > 0 then 1 else 0 end) as betonriver,sum(case when ph.maxstreetseen >= 3 then 1 else 0 end) as sawriver 
                from playerhandscashkeycolumns_hero ph join players pl on (pl.player_id = ph.player_id) 
                 join gametypes gt on gt.gametype_id = ph.gametype_id  where (ph.player_id = {0})
                    and ph.handtimestamp between to_timestamp('{1}','yyyy/MM/dd HH:mm:ss') and to_timestamp('{2}','yyyy/MM/dd HH:mm:ss hh24:mi:ss')  and gt.pokergame = 1
                    group by  gt.gametypedescription,gt.bigblind 
                     order by bigblindvalue asc;",
                    playerId, _formatSQLDateToString(startDate, true), _formatSQLDateToString(endDate, true));

        }

        /// <summary>
        /// Query to get full stats for a player and a periode, and grouped by gametype
        /// </summary>
        /// <param name="playerId">The id of the player</param>
        /// <param name="startDate">The start Date</param>
        /// <param name="endDate">The end date</param>
        /// <returns></returns>
        public static string GetSqlSimplifiedStats(Int32 playerId, DateTime startDate, DateTime endDate)
        {

            return
                string.Format(@" SELECT gt.gametypedescription as gametype,ph.player_id
                ,gt.bigblind as bigblindvalue,count(ph.pokerhand_id) as totalhands
		,sum(ph.netamountwon) as netamountwon
		,sum(ph.netamountwon/1.0/gt.bigblind) as totalbbs,sum(case when didvpip = true then 1 else 0 end) as vpiphands
        FROM playerhandscashkeycolumns ph
        join gametypes gt on gt.gametype_id = ph.gametype_id
         where (ph.player_id = {0}) AND ph.handtimestamp Between TIMESTAMP '{1}' AND TIMESTAMP '{2}'
	     group by  gt.gametypedescription,gt.bigblind ,ph.player_id
          order by bigblindvalue asc;",
                    playerId, _formatSQLDateToString(startDate, true), _formatSQLDateToString(endDate, true));

        }

    }
}
